D E L I M I T A File Conversion Utility Copyright 1993 Roy G Grosbach April 1993 Version 3.27 TABLE OF CONTENTS WHAT IS SHAREWARE . . . . . . . . . . . . . . . . . . . . . . 3 Why DELIMIT . . . . . . . . . . . . . . . . . . . . . . . . . 3 _______ History of DELIMIT . . . . . . . . . . . . . . . . . . . . . 4 _______ FUTURE ENHANCEMENTS . . . . . . . . . . . . . . . . . . . . . 5 QUICK REFERENCE . . . . . . . . . . . . . . . . . . . . . . . 6 Parameter File Format . . . . . . . . . . . . . . . . . . . . 8 Record Selection . . . . . . . . . . . . . . . . . . . . 8 Header Parm . . . . . . . . . . . . . . . . . . . . 8 Detail Parm . . . . . . . . . . . . . . . . . . . . 8 Comment . . . . . . . . . . . . . . . . . . . . . . . . 8 File Format: . . . . . . . . . . . . . . . . . . . . . . 9 Output File . . . . . . . . . . . . . . . . . . . . 9 Separator Parm . . . . . . . . . . . . . . . . 9 Record Separator Parm . . . . . . . . . . . . 9 Input file . . . . . . . . . . . . . . . . . . . . 9 Length parm . . . . . . . . . . . . . . . . . 9 Data Selection . . . . . . . . . . . . . . . . . . . . 10 Character Parm . . . . . . . . . . . . . . . . . 10 Move Parm . . . . . . . . . . . . . . . . . . . . 10 Number Parm . . . . . . . . . . . . . . . . . . . 11 Signed Numeric Parm . . . . . . . . . . . . . . . 11 EXAMPLES . . . . . . . . . . . . . . . . . . . . . . . . . 12 BANK1.PAR . . . . . . . . . . . . . . . . . . . . . . 13 BANK2.PAR . . . . . . . . . . . . . . . . . . . . . . 17 DATE1.PAR . . . . . . . . . . . . . . . . . . . . . . 20 DATE2.PAR . . . . . . . . . . . . . . . . . . . . . . 22 DATE2MAC.PAR . . . . . . . . . . . . . . . . . . . . . 25 DISCLAIMER - AGREEMENT . . . . . . . . . . . . . . . . . . 27 SOFTWARE SUPPORT . . . . . . . . . . . . . . . . . . . . . 28 HARDWARE REQUIREMENTS . . . . . . . . . . . . . . . . . . . 28 REGISTRATION FORM . . . . . . . . . . . . . . . . . . . . . 29 2 WHAT IS SHAREWARE? Shareware distribution gives users a chance to try software before buying it. If you try a Shareware program and continue using it, you are expected to register. Individual programs differ on details -- some request registration while others require it, some specify a maximum trial period. With registration, you get anything from the simple right to continue using the software to an updated program with printed manual. Copyright laws apply to both Shareware and commercial software, and the copyright holder retains all rights, with a few specific exceptions as stated below. Shareware authors are accomplished programmers, just like commercial authors, and the programs are of comparable quality. (In both cases, there are good programs and bad ones!) The main difference is in the method of distribution. The author specifically grants the right to copy and distribute the software, either to all and sundry or to a specific group. For example, some authors require written permission before a commercial disk vendor may copy their Shareware. Shareware is a distribution method, not a type of software. You should find software that suits your needs and pocketbook, whether it's commercial or Shareware. The Shareware system makes fitting your needs easier, because you can try before you buy. And because the overhead is low, prices are low also. Shareware has the ultimate money-back guarantee -- if you don't use the product, you don't pay for it. Why DELIMIT? _______ DELIMIT converts fixed format files into DELIMITed files, that _______ data bases and spread sheets can read quickly. DELIMIT filters _______ the file, choosing selected fields, eliminating headers from reports, or selecting only summary lines from reports. DELIMIT _______ can also choose selected fields from fixed-format files easily. Spread sheets can also parse fixed format files, but not as quickly as DELIMIT. DELIMIT can remove header records, or select _______ _______ detail records, spread sheets can not do this, it must be done manually. Data bases can read fixed format files, but can not choose selected fields. This must be done either manually or by writing a program. A program can be written to exclude headers, or include summary records - but building a DELIMIT parameter (parm) _______ file is faster and easier than writing an entire program. 3 Microsoft Windows users can cut and paste data from a fixed format file, or from a report file, but this is slow and tedious. If you need to do more than several screens worth of data, building a DELIMIT parm file is faster and easier, because you _______ are replacing the cut & paste process. Once the parm file is created it can be used next time you need data from that report again. History of DELIMIT _______ DELIMIT was created when a friend needed to do some budget _______ analysis. The accounting reports were 100 pages long, and we needed to get the department totals into a spread sheet for analysis. There were 3 choices: 1) Ask the computer operators to create an ad hoc report in a difficult to use report writing system, and import it into the spread sheet. They were back logged, so it would have taken a couple of weeks, (and the next time the requirements would have been different, and we would have to wait weeks again.) 2) Import the entire report into the spread sheet, and delete the detail records. (the spread sheet couldn't handle that many lines). 3) Use a text editor to delete the detail lines, and then import it into the spread sheet. This would have been very tedious! We tested parsing 1 page of the accounting report in a spread sheet program. Reading the text file was quick, but parsing it took a long time - it would have literally taken HOURS to parse the hundred pages of data we needed. We didn't need all the columns that were included on the report anyway. DELIMIT v1.00 was created. We could INCLUDE the records we _______ wanted and ignore the rest (Detail parm). We could also select the columns we wanted and ignore the others. The data was either text - surrounded by double quotes, (Character parm) or numbers (Number parm). What would have taken hours to parse took just a few minutes using DELIMIT. The spread sheet program could read _______ the text file quickly, and the time consuming parse was not needed. Several weeks later, we needed to do the same thing, but wanted all the detail records for a 20 page section of the report, not just the totals. There was no unique way to identify the detail records, but we could identify the records we didn't want. This is when the Header parm was added, to select records to EXCLUDE from the processing, and process the rest. Somewhere along the line, someone else needed to DELIMIT a file, _______ but their destination program didn't want text surrounded by double quotes. This is when the Move parameter was created, to select the text, but not surround it with double quotes. 4 Another person saw this and said, "this is great!, but why do I have to print the report first, why can't I just copy the data file from the mini computer?" This represented a two changes. The first change was that the source file was no longer a report. The data file from the mini was fixed format, with NO record separators. This is when the Length parm was created, to be able to read files with no record separators. The second problem was that the records were longer than 255 characters. The input buffers can now work with records up to 4000 bytes. During another project, we needed to bring a data file from a main frame tape, to a Macintosh (Mac). The Mac doesn't understand comma delimited files, it doesn't like double quotes or Carriage Return - Line Feed (CR-LF) for the record separator either. This produced the Separator parm, to be able to use any other character (or string) as a field separator. This also produced the Record separator parm, in able to use any other character (or string) as the record separator. After explaining the program to someone, they said, "how can you understand what all those parm files mean?". This lead to the * comment parm, so parm files can be documented. The latest project has been to acquire data from many diverse sources, and import it into a data base. One mainframe source could only supply data on large reel tape. The numeric fields were signed (the last position not a number it is a letter). This produced the Signed Numeric field. It is faster to create one import program, and many DELIMIT parm _______ files to create the format we need, than to continue to write new import programs for the data base. Also the research analysts don't want to program, but they love to DELIMIT. _______ FUTURE ENHANCEMENTS - A constant parm, to add 'constant' data to every record. During one project, data is reformatted from a fixed format file to be imported into a spread sheet. For formatting purposes, the user wants to create blank columns. Currently we just select an area we know will be blank to give the empty field. - A record count parm. Some data by its nature does not have unique keys. A record count parm could be used to create unique keys. Some data bases require a unique key for storage reasons. - A prettier user interface. 5 QUICK REFERENCE Program DELIMIT Environment DOS, Turbo Pascal 3.0 - 7.0 Author Roy Grosbach Copyright Copyright 1987-1993 Roy Grosbach Date DELIMIT V3.27 TP7.0 04/25/93 Some of DELIMIT's MANY uses: _______ - Convert a print file (report printed to disk) to a file to be read into a spread sheet or data base (remove the report headers and convert numbers). - Convert a fixed format file to a comma (or tab) delimited file. - Extract selected fields (i.e. totals from a print file, to be read into a spread sheet or data base, disregard all other fields). - Extract selected fields from a fixed record format (mainframe data base) for importing to a data base. - Extract selected fields from a fixed format file and create a smaller fixed format file. - Reformat a fixed format file into a different fixed format file. (data conversion from one system to another). The Numeric fields in comma (or tab) delimited files are expected NOT to have commas or other non numeric characters. The negative sign is expected to be in the first position of the field. Many reports use a negative sign at the last position of the field, DELIMIT will move it _______ to the front. Other reports use parenthesis to inclose negative amounts, which DELIMIT removes, and places a negative sign in the _______ first position. Text fields may be moved as is (Move parameter), or surrounded by double quotes (Character parameter). Fields are separated by commas, or by a user defined character, (Separator parameter) or by nothing. IF fields are separated by NO character, the fields are fixed format. IF fields are separated by any 'separator', extra spaces are trimmed from the fields. Records (lines) are separated by CR-LF (Carriage Return-Line Feed), by a user defined character (Record separator parameter), or by nothing. Command line: DELIMIT 6 QUICK REFERENCE Valid PARAMETER TYPES * - comment * All text is ignored. H - Header H {comments} text to be excluded (all lines that match are excluded from conversion). Up 10 Headers parms. D - Detail D {Comments} text to be included (ONLY records that match are converted). Up to 10 Detail parms. N - Numeric N {Comments} Leading zeros and commas are removed. Formatting for negative numbers is removed and a '-' placed at the beginning of the field. IF the field separator is Snone or Rnone, then spaces are included to right justify the field. @ - Signed Numeric @ {Comments} Signed fields from the mainframe replace the LAST position with a character that represents the number AND the sign (positive or negative). Leading zeros are removed. IF the field separator is Snone or Rnone, then spaces are included to right justify the field. C - Character C {Comments} Surrounds field with double quotes. Trailing blanks are removed. M - Move M {Comments} Does not surround with quotes, trailing spaces are removed. IF the field separator is Snone or Rnone, then trailing spaces are NOT removed. L - Length L {Comments} Length of fixed records (that have no CR-LF). Sometimes data files from main frames have no end of record marker. Can be up to 4000. S - Separator S Change the field separator from a comma to something else. ** must NOT have a space between the 'S' and the separator S\nnn - nnn = ASCII code R - Record Sep R Change the record separator from a CR-LF to something else. ** must NOT have a space between the 'R' and the separator R\nnn - nnn = ASCII code ** NOTE Snone or Rnone replaces the separator with NOTHING! 7 Parameter File Format There are four basic types of parameters (parms), Record Selection, File Format, Data Selection, and Comments. The Record Selection parms and Data Selection parms need to know the position, and length of a field. The Record selection parms also need to know the position and length of a string, to compare with it's "matching string" field. The File Format parms provide new separator characters for the output file, and the length parm denotes if the input file has no record separators. The Comment allows the user to document the parm file. A. Record Selection: Header Parm: Identifies records to be excluded (don't use a record if it matches one of these strings). There may be up to 10 header parms in a parm file, and they may appear in any order in the file. Format: H {comments} Detail Parm: Identifies records to be included (use ONLY the records that match one of these strings). There may be up to 10 detail parms in a parm file, and they may appear in any order in the file. Format: D {comments} B. Comment: Any line in the parm file that begins with a '*' is considered a comment, and is ignored. Format: * 8 C. File Format: Output File: Separator Parm: This defines the field separators. The default field separator is a comma. Only one Field Separator is allowed per parm file, and it may appear anywhere in the file. Format: Snone Snone will allow you to have NO field separators. This is useful when you are reformatting a file, or need to create a fixed format file. The MOVE and NUMBER parms will NOT remove leading or trailing spaces when Snone is selected, they will when any other record separator is selected. S\nnn\nnn... Where \nnn are ASCII character codes. S\009 To use a TAB for a separator (useful for the Macintosh) Record Separator Parm: This defines the record separators. The default record separator is a CR-LF. Only one Record Separator parm is allowed per parm file, and it may appear anywhere in the file. Format: Rnone Rnone will allow you to have NO record separators. This is useful when you are reformatting a file for a computer system that does not want record separators. The MOVE and NUMBER parms will NOT remove leading or trailing spaces when Rnone is selected, they will when any other record separator is selected. R\nnn\nnn... Where \nnn are the ASCII codes of characters. R\013 To use a CR for a separator (useful for a Macintosh) Input file: Length parm: This is used to define the length of a fixed format file that does NOT have record separators. Some computer files do not have record separators. Format: L n Where n is an integer less than or equal to 4000. 9 D. Data Selection: There may be up to 255 Data Selection parms. The order of the fields in the output file is determined by the order of the parms in the parm file (i.e. the first Data Selection parm will be the first field in the output file). The field is based on the position, and the length of the field desired. characters are copied, starting at . NO error checking is done on the input fields. If an input line is shorter than the expected position of a field, the field is ignored, and no delimiter is added. Characters are processed as is - the 'correctness' of the output file is the responsibility of the user. The user must insure that the position and length fields are correct. DELIMIT does not generate any warning _______ messages. Character Parm: The field is surrounded by double quotes. Trailing spaces in the field are removed. Format: C {Comment} Move Parm: The field is NOT surrounded by double quotes. Trailing spaces are removed. IF the record separator is RNONE, then trailing spaces are NOT removed (so you can build fixed format files). Format: M {Comment} 10 Number Parm: The field is reformatted. All non-numeric characters are removed, commas are removed - because they are normally considered field separators in text files. Formatting for negative numbers are eliminated: A trailing '-' is moved to the beginning of the field. A field surrounded by brackets '()' has a '-' placed at the beginning of the field. IF the record separator is RNONE, the field is LEFT filled with leading spaces (so you can build fixed format files). If other non-numeric characters are found, DELIMIT DOES NOT _______ report an error. It is up to the user to make sure the field is defined correctly. Format: N {Comment} Signed Numeric Parm: Signed Numeric data comes from mainframes. The sign AND the value is contained in the last byte of the field. "A" is a positive 1, "B" is a positive 2 and so on. The signed numeric parm does all the same reformatting as the Number parm, AND the conversion of the last byte. The field is reformatted. All non-numeric characters are removed, commas are removed - because they are normally considered field separators in text files. Formatting for negative numbers are eliminated: The last byte is translated. If it is negative, a '-' is placed at the beginning of the field. IF the record separator is RNONE, the field is LEFT filled with leading spaces (so you can build fixed format files). If other non-numeric characters are found, DELIMIT DOES NOT _______ report an error. It is up to the user to make sure the field is defined correctly. Format: @ {Comment} 11 EXAMPLES The examples are designed to demonstrate the functionality of DELIMIT. _______ The following examples can be seen by running the corresponding .BAT files. 1. Using the Heading Parm. In this example we will extract fields from a printed report (printed to disk). We want to EXCLUDE the report headings. EX1.BAT BANK1.PAR EX1.BAT BANK1.PAR 2. Using the Detail Parm. In this example we will extract fields from a printed report (printed to disk). We will use the same input file as example number one, but we will INCLUDE just the detail lines. EX2.BAT BANK2.PAR EX2.BAT BANK2.PAR 3. Reformatting a fixed format file. Field separator is None. In this example we will reformat the date field of a fixed format record (from YY/MM/DD to MM/DD/YY). We will leave the rest of the record in the same format for later processing. EX3.BAT DATE1.PAR EX3.BAT DATE1.PAR 4. Convert a fixed format record into a comma delimited file. We will take selected fields from a fixed format record, and convert it into a comma delimited file. This example uses the output of example 3 as the input of this example. EX4.BAT DATE2.PAR EX4.BAT DATE2.PAR 5. Macintosh Example. The Macintosh computer uses TABs to separate fields, and a CR (not CR-LF) to separate records. We will use the output file created in example 3 as the input of this example. EX5.BAT DATE2MAC.PAR EX5.BAT DATE2MAC.PAR 6. Creating files with no record separators. This example shows how DELIMIT can create files with no record _______ separators. We will use the input file from example 3 and convert it into a file with no record separators. EX6.BAT DATE6.PAR EX6.BAT DATE6.PAR 7. Reading Files with no record separators. We will extract selected fields from the file without record separators created in example 6. Our output file will be TAB delimited. EX7.BAT DATE7.PAR EX7.BAT DATE7.PAR Examples 6 and 7 are not in the printed documentation. The files are provided on the distribution disk. 12 Example 1. BANK1.PAR You work at a bank, and you need to do some analysis on the Adjustable Rate Mortgages that your banking software does not perform. You have several options: 1) key in the data you need. 2) open a terminal session to your banking system, and cut & paste the data. If the data is not in the proper order, or the block is large, this is a very tedious process. 3) Use DELIMIT. If you do this analysis more than once, then the _______ work is done for you for the second time. What do we need to do to the ARM (Adjustable Rate Mortgage) report to move the data into a spread sheet? - Remove the report headings - Identify the account number, branch number, change of rate, and remaining balance. There are two ways to identify the report headings and the data fields: 1) Print the first page of the report, and use a forms ruler to calculate the positions and lengths of the fields. 2) Open BANK.TXT with a text editor or word processor that can read and write text files and display the position of the cursor so we can determine the length of headers, and fields we want to capture. If your editor can have multiple windows open, you can jump back and forth between the data file window and the parm file window to create your parm file. The parm file must be a plain ASCII file, most word processors can create a plain ASCII file. INPUT FILE: BANK.TXT INT110 FIRST NATIONAL BANK PAGE 1 TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ACCOUNT BR NAME MATURE OLD CHANGE NEW BALANCE RATE RATE 123456 01 MYRON MYERS 12/31/2001 10.250 0.250- 10.000 40,392.25 123847 02 SUSAN SMITH 12/31/1999 11.500 0.250- 11.250 85,934.65 129087 02 JOHN Q PUBLIC 08/31/2006 9.785 0.215 10.000 36,900.98 138264 03 ANDREW ANDERSON 01/31/2010 8.375 0.250 8.626 135,968.99 149821 03 PAUL PUBLIC 07/30/1998 13.250 0.250- 13.000 56,654.15 298743 04 LEWIS LEAPYEAR 02/29/2004 12.500 0.250- 12.250 12,125.54 E N D O F R E P O R T 13 First, identify this Parm file, so others can easily identify what we are trying to do. Build a 'flower box' of asterisks, and copy in the first several lines of the report. Then add some text describing what we intend to do. ******************** *INT110 FIRST NATIONAL BANK PAGE 1 *TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ******************** *EXAMPLE BANK1.PAR * Ignore the headers * Extract the Account number, Branch, Interest rate change, * new interest rate and the loan balance. ******************** We need to identify every heading line so we can eliminate them. The first line can be identified by the word PAGE. PAGE will probably be in the same position for EVERY report generated by this banking system. PAGE is located at the 71st position of the print line, and is 4 characters long. Add the line "H 71 4 PAGE" to the parameter file. INT110 FIRST NATIONAL BANK PAGE 1 The second header line can be identified by the word TIME. Just as with PAGE, TIME will probably be in the same position for EVERY report generated by this system. TIME is located in the 1st position of the print line and is 4 characters long. Add the line "H 1 4 TIME" to the parameter file. TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 The third header line can be uniquely identified by the word "ACCOUNT". It is located at the 1st position of the print line and is 7 characters long. Add the line "H 1 7 ACCOUNT" to the parameter file. ACCOUNT BR NAME MATURE OLD CHANGE NEW BALANCE The fourth header line can be identified by the word RATE. RATE is located in the 46th position of the print line and is 4 characters long. Add the line "H 46 4 RATE" to the parameter file. RATE RATE 14 We must not forget the footer on the report. If it is not identified, then this line will also be processed, with unforeseen consequences. In this case it would be a record with blank numeric fields. To identify a unique sequence of characters we can select the entire phrase: "E N D O F R E P O R T". The footer is located at the 28th character of the print line and is 25 characters long. Add the line "H 28 25 E N D O F R E P O R T" to the parameter file. E N D O F R E P O R T Now we are ready to identify the data we want for further analysis. The first field is the Account Number. The Account Number is numeric, but it could also have characters in the field, so we should treat it as a character field. Also, a N parm would remove leading zeros. Account Number begins in the 1st column of the print file. Currently the Account Number is 6 characters, but it could grow to be 7 characters. Add the line "C 1 7 Account Number" to the parameter file. ("Account Number" is treated as additional comments) The second field is the branch. Branch could also have non-numeric data in it so treat it as a character field. The Branch begins in the 10th position on the print line and is 2 characters long. Add the line "C 10 2 Branch" to the parameter file. The third field we want is the Change field. It will ONLY be numeric, and the negative sign must be moved to the beginning of the field. Change starts at position 53 and is 6 characters long (including the negative sign). Add the line "N 53 6 change" to the parameter file. The fourth field is the New Rate field. It starts at position 61 and is 6 characters long. Add the line "N 61 6 new rate" to the parameter file. The last field is the remaining Balance field. Leading spaces will be ignored by numeric fields. Be sure to define the field to get the largest amounts. To do this, the balance field starts at position 69 of the print line and is 10 characters long. Add the line "N 69 10 balance" to the parameter file. 1 2 3 4 5 123456 01 MYRON MYERS 12/31/2001 10.250 0.250- 10.000 40,392.25 123847 02 SUSAN SMITH 12/31/1999 11.500 0.250- 11.250 85,934.65 129087 02 JOHN Q PUBLIC 08/31/2006 9.785 0.215 10.000 36,900.98 138264 03 ANDREW ANDERSON 01/31/2010 8.375 0.250 8.625 135,968.99 149821 03 PAUL PUBLIC 07/30/1998 13.250 0.250- 13.000 56,654.15 298743 04 LEWIS LEAPYEAR 02/29/2004 12.500 0.250- 12.250 12,125.54 15 PARAMETER FILE: BANK1.PAR ******************** *INT110 FIRST NATIONAL BANK PAGE 1 *TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ******************** *EXAMPLE BANK1.PAR * Ignore the headers * Extract the Account number, Branch, Interest rate change, * new interest rate and the loan balance. ******************** H 71 4 PAGE H 1 4 TIME H 1 7 ACCOUNT H 46 4 RATE H 28 25 E N D O F R E P O R T M 1 6 Account number M 10 2 Branch N 53 6 change N 61 6 new rate N 69 10 balance Snone OUTPUT FILE: BANK1.OUT 12345601 -.25010.000 40392.25 12384702 -.25011.250 85934.65 12908702 .21510.000 36900.98 13826403 .250 8.626 135968.99 14982103 -.25013.000 56654.15 29874304 -.25012.250 12125.54 16 Example 2. BANK2.PAR Example 2 uses the same input file as example 1. Instead of using the Header parm to exclude header records, it uses the Detail parm to include only the detail lines to process. We can identify the detail lines by the fact they all have a maturity date. The date field always has a '/' at a particular place. No other line in the file has a slash in that position. INPUT FILE: BANK.TXT INT110 FIRST NATIONAL BANK PAGE 1 TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ACCOUNT BR NAME MATURE OLD CHANGE NEW BALANCE RATE RATE 123456 01 MYRON MYERS 12/31/2001 10.250 0.250- 10.000 40,392.25 123847 02 SUSAN SMITH 12/31/1999 11.500 0.250- 11.250 85,934.65 129087 02 JOHN Q PUBLIC 08/31/2006 9.785 0.215 10.000 36,900.98 138264 03 ANDREW ANDERSON 01/31/2010 8.375 0.250 8.626 135,968.99 149821 03 PAUL PUBLIC 07/30/1998 13.250 0.250- 13.000 56,654.15 298743 04 LEWIS LEAPYEAR 02/29/2004 12.500 0.250- 12.250 12,125.54 E N D O F R E P O R T First, identify this Parm file, so others can easily identify what we are trying to do. Build a 'flower box' of asterisks, and copy in the first several lines of the report. Then add some text describing what we intend to do. ******************** *INT110 FIRST NATIONAL BANK PAGE 1 *TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ******************** *EXAMPLE BANK2.PAR * Use ONLY records that have a '/' in the 35th position * (the slash between MM and DD) * Extract the Account number, Branch, Interest rate change, * new interest rate and the loan balance. ******************** 17 We only want records that have a '/' in the 35th position. This is part of the MATURE DATE field. Add the line "D 35 1 /" to the parameter file. * 123456 01 MYRON MYERS 12/31/2001 10.250 0.250- 10.000 40,392.25 123847 02 SUSAN SMITH 12/31/1999 11.500 0.250- 11.250 85,934.65 129087 02 JOHN Q PUBLIC 08/31/2006 9.785 0.215 10.000 36,900.98 138264 03 ANDREW ANDERSON 01/31/2010 8.375 0.250 8.626 135,968.99 149821 03 PAUL PUBLIC 07/30/1998 13.250 0.250- 13.000 56,654.15 298743 04 LEWIS LEAPYEAR 02/29/2004 12.500 0.250- 12.250 12,125.54 E N D O F R E P O R T The rest of the parms are exactly the same as example 1. You can either copy them from that file, or follow the instructions from the example, they should look like this: C 1 6 Account number C 10 2 Branch N 53 6 change N 61 6 new rate N 69 10 balance 18 INPUT FILE: BANK.TXT INT110 FIRST NATIONAL BANK PAGE 1 TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ACCOUNT BR NAME MATURE OLD CHANGE NEW BALANCE RATE RATE 123456 01 MYRON MYERS 12/31/2001 10.250 0.250- 10.000 40,392.25 123847 02 SUSAN SMITH 12/31/1999 11.500 0.250- 11.250 85,934.65 129087 02 JOHN Q PUBLIC 08/31/2006 9.785 0.215 10.000 36,900.98 138264 03 ANDREW ANDERSON 01/31/2010 8.375 0.250 8.626 135,968.99 149821 03 PAUL PUBLIC 07/30/1998 13.250 0.250- 13.000 56,654.15 298743 04 LEWIS LEAPYEAR 02/29/2004 12.500 0.250- 12.250 12,125.54 E N D O F R E P O R T PARAMETER FILE: BANK2.PAR ******************** *INT110 FIRST NATIONAL BANK PAGE 1 *TIME 19:25 ADJUSTABLE RATE MORTGAGE CHANGES DATE 12/31/87 ******************** *EXAMPLE BANK2.PAR * Use ONLY records that have a '/' in the 35th position * (the slash between MM and DD) * Extract the Account number, Branch, Interest rate change, * new interest rate and the loan balance. ******************** D 35 1 / C 1 6 Account number C 10 2 Branch N 53 6 change N 61 6 new rate N 69 10 balance OUTPUT FILE: BANK2.OUT "123456","01",-.250,10.000,40392.25 "123847","02",-.250,11.250,85934.65 "129087","02",.215,10.000,36900.98 "138264","03",.250,8.626,135968.99 "149821","03",-.250,13.000,56654.15 "298743","04",-.250,12.250,12125.54 19 Example 3. DATE1.PAR We have received the employee master file from the company mini computer. The date field is in the format (YY/MM/DD). To convert this file into a comma DELIMITed file is a two step process. The first step is to reformat the date to (MM/DD/YY) and leave the rest of the record the same (this example). The second step is to DELIMIT it (example 4). _______ INPUT FILE: DATE.TXT 12345662/05/15MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384750/12/12SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908758/06/25JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826467/04/30ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982155/03/31PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874360/02/29LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDEN CO02500 First, identify this Parm file, so others can easily identify what we are trying to do. Build a 'flower box' of asterisks, and copy in the first several lines of the report. Then add some text describing what we intend to do. ******************* *Employee master file - from the mini ******************* *EXAMPLE DATE1.PAR * The master file is in a fixed file format. The date of birth is stored * in YY/MM/DD format. The target program doesn't understand this format. * 1) Move the employee number (6 bytes) * 2) Move the month and day * 3) Move the slash * 4) Move the year (now at the end of the date) * 5) Move the rest of the data * 6) USE NO SEPARATORS - so we can use delimit again to break the * records into fields. ******************** 20 All we want to do is rearrange the fields, so we will only be using the Move parameters. We must also specify Snone so that no separators are used. The first field is the Employee number. The second field is the month/day. The third field is the slash (to put between the month/day and the year. The forth field is the year. The fifth field is the rest of the record. 1 4 3 2 5 * 12345662/05/15MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384750/12/12SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908758/06/25JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826467/04/30ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982155/03/31PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874360/02/29LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDEN CO02500 PARAMETER FILE: DATE1.PAR ******************* *Employee master file - from the mini ******************* *EXAMPLE DATE1.PAR * The master file is in a fixed file format. The date of birth is stored * in YY/MM/DD format. The target program doesn't understand this format. * 1) Move the employee number (6 bytes) * 2) Move the month and day * 3) Move the slash * 4) Move the year (now at the end of the date) * 5) Move the rest of the data * 6) USE NO SEPARATORS - so we can use delimit again to break the * records into fields. ******************** M 1 6 Employee number M 10 5 month and day M 9 1 slash M 7 2 year M 15 63 rest of data Snone OUTPUT FILE: DATE1.OUT 12345605/15/62MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384712/12/50SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908706/25/58JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826404/30/67ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982103/31/55PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874302/29/60LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDON CO02500 21 Example 4. DATE2.PAR Now we want to delimit the fields from Example 3 into a comma delimited file. INPUT FILE: DATE1.OUT 12345605/15/62MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384712/12/50SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908706/25/58JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826404/30/67ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982103/31/55PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874302/29/60LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDEN CO02500 First, identify this Parm file, so others can easily identify what we are trying to do. Build a 'flower box' of asterisks, and copy in the first several lines of the report. Then add some text describing what we intend to do. PARAMETER FILE: DATE2.PAR ******************* *Employee master file - from the mini - AFTER THE DATE HAS BEEN REFORMATTED ******************* *EXAMPLE DATE2.PAR * Break the record into fields: * Employee number, date of birth, first name, last name, address, * city, state, monthly salary ******************** 22 The first field is the employee number. Identification numbers should always be treated as character fields. It starts in the first column, and is 6 characters long. Add the line "C 1 6 Employee number" to the parameter file. The second field is the date. It starts at column 7 and is 8 characters long. Add the line "C 7 8 date" to the parameter file. The third fields is the first name. It starts at column 15 and is 9 characters long. Add the line "C 15 9 first name" to the parameter file. The forth field is the last name. It starts at column 24 and is 13 characters long. Add the line "C 24 13 last name" to the parameter file. The fifth field is the street address. It starts at column 37 and is 22 characters long. Add the line "C 37 22 address" to the parameter file. The sixth field is the city. It starts at column 59 and is 12 characters long. Add the line "C 59 12 city" to the parameter file. The seventh field is the state. It starts at column 71 and is 2 characters long. Add the line "C 71 2 state" to the parameter file. The eight field is the salary field. It is numeric, and starts at column 73 and is 5 characters long. Add the line "C 73 5 monthly salary" to the parameter file. 1 2 3 4 5 6 7 8 12345605/15/62MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384712/12/50SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908706/25/58JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826404/30/67ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982103/31/55PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874302/29/60LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDEN CO02500 23 PARAMETER FILE: DATE2.PAR ******************* *Employee master file - from the mini - AFTER THE DATE HAS BEEN REFORMATED ******************* *EXAMPLE DATE2.PAR * Break the record into fields: * Employee number, date of birth, first name, last name, address, * city, state, monthly salary ******************** C 1 6 Employee number C 7 8 date C 15 9 first name C 24 13 last name C 37 22 address C 59 12 city C 71 2 state N 73 5 monthy salary OUTPUT FILE: DATE2.OUT "123456","05/15/62","MYRON","MYERS","123 S 68TH STREET","AURORA","CO",1500 "123847","12/12/50","SUSAN","SMITH","1955 W ANY STREET","LITTLETOWN","CO",1700 "129087","06/25/58","JOHN Q","PUBLIC","123 N AMERICAN","DENVER","CO",3300 "138264","04/30/67","ANDREW","ANDERSON","5633 E OAK","FT COLLINS","CO",10000 "149821","03/31/55","PAUL","PUBLIC","8934 MAIN #108","DENVER","CO",2000 "298743","02/29/60","LEWIS","LEAPYEAR","2929 MAPLE DRIVE","GOLDON","CO",2500 24 Example 5. DATE2MAC.PAR In this example we are converting the employee master file to be used on a Macintosh computer. The Macintosh computer uses TAB delimited files, with a CR (not CR-LF) to denote the end of record. We will use the output file generated in example 3 as our input (example 3 reformatted a fixed format file). INPUT FILE: DATE1.OUT 12345605/15/62MYRON MYERS 123 S 68TH STREET AURORA CO01500 12384712/12/50SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO01700 12908706/25/58JOHN Q PUBLIC 123 N AMERICAN DENVER CO03300 13826404/30/67ANDREW ANDERSON 5633 E OAK FT COLLINS CO10000 14982103/31/55PAUL PUBLIC 8934 MAIN #108 DENVER CO02000 29874302/29/60LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDEN CO02500 We will copy the parm file from example 4 (comma delimiting the employee master file) and make a few minor adjustments. ******************* *Employee master file - from the mini - AFTER THE DATE HAS BEEN REFORMATTED * TARGET MACHINE IS THE MACINTOSH ******************* *EXAMPLE DATE2MAC.PAR * Break the record into fields: * Employee number, date of birth, first name, last name, address, * city, state, monthly salary. * S\009 is to change the field separators to a TAB (ASCII char 9) * R\013 is to change the record separators to a CR (ASCII char 13) ******************** The 'standard' text files on a Macintosh are different than on a PC. First, the Mac separates fields with TABs, not commas. It separates records with a CR, not a CR-LF. It also does NOT surround fields with double quotes. Add the S\009 (field Separator = TAB), R\013 (Record separator = CR), and change the Character parameters to Move parameters. M 1 6 Employee number M 7 8 date M 15 9 first name M 24 13 last name M 37 22 address M 59 12 city M 71 2 state N 73 5 monthly salary S\009 R\013 25 PARAMETER FILE: DATE2MAC.PAR ******************* *Employee master file - from the mini - AFTER THE DATE HAS BEEN REFORMATED * TARGET MACHINE IS THE MACINTOSH ******************* *EXAMPLE DATE2MAC.PAR * Break the record into fields: * Employee number, date of birth, first name, last name, address, * city, state, monthly salary. * S\009 is to change the field separators to a TAB (ASCII char 9) * R\013 is to change the record separators to a CR (ASCII char 13) ******************** M 1 6 Employee number M 7 8 date M 15 9 first name M 24 13 last name M 37 22 address M 59 12 city M 71 2 state N 73 5 monthy salary S\009 R\013 OUTPUT FILE: DATE2MAC.OUT 123456 05/15/62 MYRON MYERS 123 S 68TH STREET AURORA CO 1500 123847 12/12/50 SUSAN SMITH 1955 W ANY STREET LITTLETOWN CO 1700 129087 06/25/58 JOHN Q PUBLIC 123 N AMERICAN DENVER CO 3300 138264 04/30/67 ANDREW ANDERSON 5633 E OAK FT COLLINS CO 10000 149821 03/31/55 PAUL PUBLIC 8934 MAIN #108 DENVER CO 2000 298743 02/29/60 LEWIS LEAPYEAR 2929 MAPLE DRIVE GOLDON CO 2500 26 DISCLAIMER - AGREEMENT Users of DELIMIT must accept this disclaimer of warranty: _______ "DELIMIT is supplied as is. The author disclaims all warranties, _______ expressed or implied, including, without limitation, the warranties of merchantability and of fitness for any purpose. The author assumes no liability for damages, direct or consequential, which may result from the use of DELIMIT." _______ DELIMIT is a "shareware program" and is provided at no charge to _______ the user for evaluation. Feel free to share it with your friends, but please do not give it away altered or as part of another system. The essence of "user-supported" software is to provide computer users with quality software without high prices, and yet to provide incentive for programmers to continue to develop new products. If you find this program useful and find that you are using DELIMIT and continue to use DELIMIT after a _______ _______ reasonable trial period, you must make a registration payment of $15 to Grosbach Software. The $15 registration fee will license one copy for use on any one computer at any one time. You must treat this software just like a book. An example is that this software may be used by any number of people and may be freely moved from one computer location to another, so long as there is no possibility of it being used at one location while it's being used at another. Just as a book cannot be read by two different persons at the same time. Commercial users of DELIMIT must register and pay for their _______ copies of DELIMIT within 30 days of first use or their license is _______ withdrawn. Site-License arrangements may be made by contacting Grosbach Software. Registered Non-profit organizations may use DELIMIT free of _______ charge. Anyone distributing DELIMIT for any kind of remuneration must _______ first contact Grosbach Software at the address below for authorization. This authorization will be automatically granted to distributors recognized by the Association of Shareware Professionals (ASP) as adhering to its guidelines for shareware distributors, and such distributors may begin offering DELIMIT _______ immediately (However Grosbach Software must still be advised so that the distributor can be kept up-to-date with the latest version of DELIMIT). _______ You are encouraged to pass a copy of DELIMIT along to your _______ friends for evaluation. Please encourage them to register their copy if they find that they can use it. 27 SOFTWARE SUPPORT You will receive three months free support via U.S. Mail or CompuServe, starting from the date your registration is received. If you believe you have discovered a bug, please send a copy of the parm file and data file and a brief description to: US Mail or CompuServe Grosbach Software Roy G Grosbach 71562,3251 PO Box 80 Lewisville, TX 75067 HARDWARE REQUIREMENTS A hard disk is not required. Tested on Monochrome, MCGA, and VGA systems. Tested on DOS version 3.30, 4.00, 5.00 and 6. Should not have problems with version 2.00 or greater. REGISTRATION If you find DELIMIT useful, and continue to use it for more than _______ 30 days, you are required to register your copy. Please use the enclosed registration form. You will be granted the right to continue to use DELIMIT. You will receive three months free _______ support via U.S. Mail or CompuServe, starting from the date your registration is received. SINGLE USER REGISTRATION: $15 fee. SITE REGISTRATION/CORPORATE REGISTRATION: 1 copy $15 2-10 copies $12.50 for each additional. 11-100 copies $10.00 for each additional over 100 copies $7.50 for each additional. EXAMPLE: You have a small business, with 10 people using computers. In good faith, you estimate at most, 3 people will be using DELIMIT on a regular basis. You also want the latest copy _______ of DELIMIT. Your cost would be $15 for the first copy, and _______ 2 x $12.50 for the additional copies, and $5.00 for the latest version of DELIMIT on disk for a total of $45. If _______ you already have the latest version, the $5.00 will be refunded, or it may be applied towards shipping of the next version of DELIMIT. _______ 28 DELIMIT REGISTRATION FORM _______ To register your copy of DELIMIT, mail this form with payment of the _______ license fee to the address at the bottom of this form. NAME: _____________________________________________ MAILING ADDRESS: _____________________________________________ _____________________________________________ _____________________________________________ COUNTRY: _____________________________________________ VERSION OF DELIMIT: ________ Where you received your copy of Delimit: Friend CompuServe BBS or Shareware Vendor ___________________________________ AMOUNT DESCRIPTION $15.00 1 X $15.00 Single User license for DELIMIT ___ $_______ ___ X $12.50 Additional licenses of DELIMIT (2-10) $_______ ___ X $10.00 Additional licenses of DELIMIT (11-100) $_______ ___ X $7.50 Additional licenses of DELIMIT (over 100) $_______ ___ X $5.00 Latest version of DELIMIT on disk ___ 5 1/4 ___ 3 1/2 ___ refunded if you have current version ___ send the NEXT version of DELIMIT _______ $_______ Texas residents add the current sales tax amount (1993 = 8.25%) $_______ Total due. Mail to: Grosbach Software P.O. Box 80 Lewisville, TX 75067 USA CompuServe: Roy G Grosbach 71562,3251 Check: Please mail checks or money orders to Grosbach Software. Checks and money orders must be drawn on a US bank in US dollars. Credit Card: Please call Public (software) Library with your MC, Visa, AmEx, or Discover card by calling 800-242-4PsL (from overseas: 713-524-6394) or by FAX to 713-524-6398 or by CompuServe to 71355,470. These numbers are for odering only. Grosbach Software can NOT be reached at those numbers. Thank you for supporting Shareware and DELIMIT. If you have any comments, or suggestions please contact Grosbach Software.